﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using BattleCore;
using BattleCore.Events;
using PsyModules;
using System.IO;

using MySql.Data.MySqlClient;
using System.Diagnostics;

namespace Battle
{
    public class BZMYSQL
    {
        // Custom chat module
        ShortChat msg = new ShortChat();  

        // Sql Vars
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        public void Initialize(Queue<EventArgs> q)
        {
            server = "123.456.7.890";
            database = "My_DB";
            uid = "My_UID";
            password = "P455w0rD";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);

            if (this.OpenConnection(q) == true)
            {
                if (this.CloseConnection(q) == true)
                    q.Enqueue(msg.arena("Battle Database configured and loaded.."));
            }
            else
                q.Enqueue(msg.arena("SQL Connection Phail."));
        }

        //open connection to database
        private bool OpenConnection(Queue<EventArgs> q)
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        q.Enqueue(msg.arena("Cannot connect to server.  Contact administrator"));
                        break;

                    case 1045:
                        q.Enqueue(msg.arena("Invalid username/password, please try again"));
                        break;
                    default:
                        q.Enqueue(msg.arena("Error: Num[" + ex.Number + "]"));
                        break;
                }
                return false;
            }
        }

        //Close connection
        private bool CloseConnection(Queue<EventArgs> q)
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                q.Enqueue(msg.arena(ex.Message));
                return false;
            }
        }

        // If the code reaches here - there is no player on the bot list to add
        // and we are checking here to see if the player is stored on the bz database
        public void CheckDB(PublicPlayer StoredPlayer, Queue<EventArgs> q)
        {
            //Open connection
            if (this.OpenConnection(q) == true)
            {
                int Bux = 0;

                string query = "SELECT bz_bux FROM bz_bank WHERE player_name = \"" + StoredPlayer.PlayerName + "\"";
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                bool exists = dataReader.HasRows;

                if (exists)
                {
                    //Read the data and store them in the list
                    while (dataReader.Read())
                    {
                        Bux = int.Parse(dataReader["bz_bux"] + "");
                    }

                    StoredPlayer.BZBux = Bux * .01;
                    //q.Enqueue(msg.arena("Existing member found- Player info Updated."));
                }
                else
                {
                    this.CloseConnection(q);
                    this.OpenConnection(q);

                    string query2 = "INSERT INTO bz_bank (player_name, bz_bux) VALUES (\"" + StoredPlayer.PlayerName + "\", 0);";
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd2 = new MySqlCommand(query2, connection);

                    //Execute command
                    cmd2.ExecuteNonQuery();

                    q.Enqueue(msg.pm(StoredPlayer.PlayerName,"You have been detected as a new client. Your bank account with BZ Bank and Trust has been created."));
                }

                if (this.CloseConnection(q) == true) { }
                    //q.Enqueue(msg.arena("Connection closed."));
            }
        }

        public void UpdatePlayer(PublicPlayer StoredPlayer, Queue<EventArgs> q)
        {
            this.OpenConnection(q);

            string query = "UPDATE bz_bank SET bz_bux = \"" + (StoredPlayer.BZBux * 100) + "\" WHERE player_name = \"" + StoredPlayer.PlayerName + "\";";
            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //Execute command
            cmd.ExecuteNonQuery();

            this.CloseConnection(q);
        }

        ////Insert statement
        //public void Insert(ChatEvent c)
        //{
        //    //string query = "INSERT INTO tableinfo (player_name, player_bux) VALUES('PsyOps', '2250')"
        //    string query = "CREATE TABLE pet (player_name VARCHAR(20), player_bux VARCHAR(20))";
        //    //CREATE TABLE pet (player_name VARCHAR(20), player_bux VARCHAR(20));

        //    //open connection
        //    if (this.OpenConnection() == true)
        //    {
        //        //create command and assign the query and connection from the constructor
        //        MySqlCommand cmd = new MySqlCommand(query, connection);

        //        //Execute command
        //        cmd.ExecuteNonQuery();

        //        //close connection
        //        this.CloseConnection();
        //    }
        //}

        ////Update statement
        //public void Update(ChatEvent c)
        //{
        //    string query = "UPDATE tableinfo SET player_name='Buny', player_bux='2210' WHERE name='PsyOps'";

        //    //Open connection
        //    if (this.OpenConnection() == true)
        //    {
        //        //create mysql command
        //        MySqlCommand cmd = new MySqlCommand();
        //        //Assign the query using CommandText
        //        cmd.CommandText = query;
        //        //Assign the connection using Connection
        //        cmd.Connection = connection;

        //        //Execute query
        //        cmd.ExecuteNonQuery();

        //        //close connection
        //        this.CloseConnection();
        //    }
        //}


        ////Delete statement
        //public void Delete(ChatEvent c)
        //{
        //    string query = "DELETE FROM tableinfo WHERE name='PsyOps'";

        //    if (this.OpenConnection() == true)
        //    {
        //        MySqlCommand cmd = new MySqlCommand(query, connection);
        //        cmd.ExecuteNonQuery();
        //        this.CloseConnection();
        //    }
        //}

        //public void getInfo(ChatEvent c)
        //{
        //    List<string>[] info = Select();
        //    Game(msg.arena("ID:[ " + info[0] + " ] Player:[ " + info[1] + " ] Money:[ " + info[2] + " ]"));
        //}
        //Select statement
        //public List<string>[] Select()
        //{
        //    string query = "SELECT * FROM tableinfo";

        //    //Create a list to store the result
        //    List<string>[] list = new List<string>[3];
        //    list[0] = new List<string>();
        //    list[1] = new List<string>();
        //    list[2] = new List<string>();

        //    //Open connection
        //    if (this.OpenConnection(q) == true)
        //    {
        //        //Create Command
        //        MySqlCommand cmd = new MySqlCommand(query, connection);
        //        //Create a data reader and Execute the command
        //        MySqlDataReader dataReader = cmd.ExecuteReader();

        //        //Read the data and store them in the list
        //        while (dataReader.Read())
        //        {
        //            list[0].Add(dataReader["id"] + "");
        //            list[1].Add(dataReader["player_name"] + "");
        //            list[2].Add(dataReader["player_bux"] + "");
        //        }

        //        //close Data Reader
        //        dataReader.Close();

        //        //close Connection
        //        this.CloseConnection(q);

        //        //return list to be displayed
        //        return list;
        //    }
        //    else
        //    {
        //        return list;
        //    }
        //}

        ////Count statement
        //public int Count()
        //{
        //    string query = "SELECT Count(*) FROM tableinfo";
        //    int Count = -1;

        //    //Open Connection
        //    if (this.OpenConnection() == true)
        //    {
        //        //Create Mysql Command
        //        MySqlCommand cmd = new MySqlCommand(query, connection);

        //        //ExecuteScalar will return one value
        //        Count = int.Parse(cmd.ExecuteScalar() + "");

        //        //close Connection
        //        this.CloseConnection();

        //        return Count;
        //    }
        //    else
        //    {
        //        return Count;
        //    }
        //}

        ////Backup
        //public void Backup()
        //{
        //    try
        //    {
        //        DateTime Time = DateTime.Now;
        //        int year = Time.Year;
        //        int month = Time.Month;
        //        int day = Time.Day;
        //        int hour = Time.Hour;
        //        int minute = Time.Minute;
        //        int second = Time.Second;
        //        int millisecond = Time.Millisecond;

        //        //Save file to C:\ with the current date as a filename
        //        string path;
        //        path = "C:\\MySqlBackup" + year + "-" + month + "-" + day +
        //    "-" + hour + "-" + minute + "-" + second + "-" + millisecond + ".sql";
        //        StreamWriter file = new StreamWriter(path);


        //        ProcessStartInfo psi = new ProcessStartInfo();
        //        psi.FileName = "mysqldump";
        //        psi.RedirectStandardInput = false;
        //        psi.RedirectStandardOutput = true;
        //        psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}",
        //            uid, password, server, database);
        //        psi.UseShellExecute = false;

        //        Process process = Process.Start(psi);

        //        string output;
        //        output = process.StandardOutput.ReadToEnd();
        //        file.WriteLine(output);
        //        process.WaitForExit();
        //        file.Close();
        //        process.Close();
        //    }
        //    catch (IOException ex)
        //    {
        //        Game(msg.arena("Error , unable to backup!"));
        //    }
        //}
        ////Restore
        //public void Restore()
        //{
        //    try
        //    {
        //        //Read file from C:\
        //        string path;
        //        path = "C:\\MySqlBackup.sql";
        //        StreamReader file = new StreamReader(path);
        //        string input = file.ReadToEnd();
        //        file.Close();

        //        ProcessStartInfo psi = new ProcessStartInfo();
        //        psi.FileName = "mysql";
        //        psi.RedirectStandardInput = true;
        //        psi.RedirectStandardOutput = false;
        //        psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}",
        //            uid, password, server, database);
        //        psi.UseShellExecute = false;


        //        Process process = Process.Start(psi);
        //        process.StandardInput.WriteLine(input);
        //        process.StandardInput.Close();
        //        process.WaitForExit();
        //        process.Close();
        //    }
            //catch (IOException ex)
            //{
            //    //Game(msg.arena("Error , unable to Restore!"));
            //}
        //}
        ////open connection
        //if (this.OpenConnection(q) == true)
        //{
        //    string query = "CREATE TABLE bz_bank ( player_id int(10) unsigned not null primary key auto_increment, player_name varchar(100) not null, bz_bux int(10) unsigned );";
        //    //create command and assign the query and connection from the constructor
        //    MySqlCommand cmd = new MySqlCommand(query, connection);

        //    //Execute command
        //    cmd.ExecuteNonQuery();

        //    //close connection
        //    this.CloseConnection(q);
        //    q.Enqueue(msg.arena("Tables saved.~"));
        //}
    }
}
